package com.fsnbigdata.repository;

import com.fsnbigdata.domain.ZhDiversityReport;
import com.fsnbigdata.domain.ZhEcologyNav;
import org.springframework.data.domain.Pageable;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Modifying;
import org.springframework.data.jpa.repository.Query;

import java.beans.Transient;
import java.util.List;

public interface DiversityReportRepository extends JpaRepository<ZhDiversityReport,Long> {

    @Modifying
    @Query(value = "DELETE FROM zh_diversity_report WHERE id=?1",nativeQuery = true)
    public void deleteOne(Long id);

    @Modifying
    @Query(value = "DELETE FROM document_diversitys WHERE diversitys_id=?1",nativeQuery = true)
    public void deleteDocument(Long id);
    
    /**
     * 标题模糊查询
     * @param title
     * @return
     */
    @Query(value = "select z from ZhDiversityReport z where title like concat('%',?1,'%')")
    public abstract List<ZhDiversityReport> findAllByTitleLike(String title);

    /**
     * 获取指定地区，指定门下面的纲，目，科，属，种数量
     * @param phylumn  门
     * @param ids  通过地区查询的物种id集合
     * @return
     */
    @Query(value = "SELECT count(DISTINCT(t.t_class)) as 纲,count(DISTINCT(t.t_order)) as 目,count(DISTINCT(t.family)) as 科,COUNT(DISTINCT(t.genus)) as 属,COUNT(t.species) as 种 FROM taxonomy t WHERE t.phylumn like concat('%',?1,'%') and t.organism_id in (?2)",nativeQuery = true)
    public Object getCountByPhylumnAndArea(String phylumn,List<Long> ids);

    /**
     * 获取指定地区，指定亚门下面的纲，目，科，属，种数量
     * @param subPhylumn  亚门
     * @param ids  通过地区查询的物种id集合
     * @return
     */
    @Query(value = "SELECT count(DISTINCT(t.t_class)) as 纲,count(DISTINCT(t.t_order)) as 目,count(DISTINCT(t.family)) as 科,COUNT(DISTINCT(t.genus)) as 属,COUNT(t.species) as 种 FROM taxonomy t WHERE t.sub_phylumn like concat('%',?1,'%') and t.organism_id in (?2)",nativeQuery = true)
    public Object getCountBySubPhylumnAndArea(String subPhylumn,List<Long> ids);

    /**
     *  获取指定地区，指定门下面的种数量
     * @param phylumn
     * @param ids
     * @return
     */
    @Query(value = "SELECT COUNT(t.species) as 种 FROM taxonomy t WHERE t.phylumn like concat('%',?1,'%') and t.organism_id in (?2)",nativeQuery = true)
    public Integer getSeed1(String phylumn,List<Long> ids);

    /**
     *  获取指定地区，指定亚门下面的种数量
     * @param subPhylumn
     * @param ids
     * @return
     */
    @Query(value = "SELECT COUNT(t.species) as 种 FROM taxonomy t WHERE t.sub_phylumn like concat('%',?1,'%') and t.organism_id in (?2)",nativeQuery = true)
    public Integer getSubSeed1(String subPhylumn,List<Long> ids);

    /**
     * 获取指定地区，指定纲下面的目，科，属，种数量
     * @param tClass  纲
     * @param ids  通过地区查询的物种id集合
     * @return
     */
    @Query(value = "SELECT count(DISTINCT(t.t_order)) as 目,count(DISTINCT(t.family)) as 科,COUNT(DISTINCT(t.genus)) as 属,COUNT(t.species) as 种 FROM taxonomy t WHERE t.t_class like concat('%',?1,'%') and t.organism_id in (?2)",nativeQuery = true)
    public Object getCountByClassAndArea(String tClass,List<Long> ids);

    /**
     * 获取指定地区，指定亚纲下面的目，科，属，种数量
     * @param supClass  亚纲
     * @param ids  通过地区查询的物种id集合
     * @return
     */
    @Query(value = "SELECT count(DISTINCT(t.t_order)) as 目,count(DISTINCT(t.family)) as 科,COUNT(DISTINCT(t.genus)) as 属,COUNT(t.species) as 种 FROM taxonomy t WHERE t.sub_class like concat('%',?1,'%') and t.organism_id in (?2)",nativeQuery = true)
    public Object getCountBySubClassAndArea(String supClass,List<Long> ids);

    /**
     *  获取指定地区，指定纲下面的种数量
     * @param t_class
     * @param ids
     * @return
     */
    @Query(value = "SELECT COUNT(t.species) as 种 FROM taxonomy t WHERE t.t_class like concat('%',?1,'%') and t.organism_id in (?2)",nativeQuery = true)
    public Integer getSeed2(String t_class,List<Long> ids);

    /**
     *  获取指定地区，指定亚纲下面的种数量
     * @param subClass
     * @param ids
     * @return
     */
    @Query(value = "SELECT COUNT(t.species) as 种 FROM taxonomy t WHERE t.sub_class like concat('%',?1,'%') and t.organism_id in (?2)",nativeQuery = true)
    public Integer getSubSeed2(String subClass,List<Long> ids);

    /**
     * 获取指定地区，指定界下面的门，纲，目，科，属，种数量
     * @param kingdom   界
     * @param ids   通过地区查询的物种id集合
     * @return
     */
    @Query(value = "SELECT COUNT(DISTINCT(t.phylumn)) as 门,count(DISTINCT(t.t_class)) as 纲,count(DISTINCT(t.t_order)) as 目, count(DISTINCT(t.family)) as 科,COUNT(DISTINCT(t.genus)) as 属,COUNT(t.species) as 种 FROM taxonomy t WHERE t.kingdom like concat('%',?1,'%') and t.organism_id in (?2)",nativeQuery = true)
    public Object getCountByKingdomAndArea(String kingdom,List<Long> ids);

    /**
     * 获取指定地区下面的界，门，纲，目，科，属，种数量
     * @param ids  通过地区查询的物种id集合
     * @return
     */
    @Query(value = "SELECT COUNT(DISTINCT(t.kingdom)) as 界,COUNT(DISTINCT(t.phylumn)) as 门,count(DISTINCT(t.t_class)) as 纲,count(DISTINCT(t.t_order)) as 目, count(DISTINCT(t.family)) as 科,COUNT(DISTINCT(t.genus)) as 属,COUNT(t.species) as 种 FROM taxonomy t WHERE t.organism_id in (?1)",nativeQuery = true)
    public Object getCountByArea(List<Long> ids);

    /**
     * 获取指定地区，指定界大于100种的大科
     * @param kingdom  界
     * @param ids  通过地区查询的物种id集合
     * @return
     */
    @Query(value = "SELECT * FROM (SELECT t.family as 名字 ,COUNT(t.species) c FROM taxonomy t WHERE t.kingdom like concat('%',?1,'%') AND t.organism_id in (?2) GROUP BY t.family) f WHERE f.c>=100",nativeQuery = true)
    public List<Object> getCountByKingdom(String kingdom,List<Long> ids);

    /**
     * 根据类型（洞穴、陆生、森林等）查询数量
     * @param habitat   类型（洞穴、陆生、森林等）
     * @param ids   通过地区查询的物种id集合
     * @return
     */
    @Query(value = "SELECT COUNT(DISTINCT(t.kingdom)) as 界,COUNT(DISTINCT(t.phylumn)) as 门,count(DISTINCT(t.t_class)) as 纲,count(DISTINCT(t.t_order)) as 目, count(DISTINCT(t.family)) as 科,COUNT(DISTINCT(t.genus)) as 属,COUNT(t.species) as 种 FROM taxonomy t WHERE t.organism_id in (SELECT o.id FROM organism o WHERE o.habitat LIKE CONCAT('%',?1,'%') AND o.id in (?2))",nativeQuery = true)
    public Object findByHabitatAndArea(String habitat,List<Long> ids);


    @Query(value = "SELECT county FROM zh_diversity_report WHERE province = '贵州' AND city = ?1 AND status = 1 group by county",nativeQuery = true)
    List<String> getCounty(String city);

    @Query(value = "SELECT city FROM zh_diversity_report WHERE province = '贵州' AND status = 1 group by city",nativeQuery = true)
    List<String> getCity();

    @Query(value = "SELECT id,title,create_time,update_time FROM zh_diversity_report WHERE status=1 and is_protection = 0 and province = '贵州' AND city = ?1 and county = ?2 ORDER BY ?#{#pageable}",
            countQuery = "SELECT count(*) FROM zh_diversity_report WHERE status=1 and is_protection = 0 and province = '贵州' AND city = ?1 and county = ?2",nativeQuery = true)
    List<Object> getPageListByCityAndCounty(String city, String county, Pageable pageable);

    @Query(value = "SELECT count(id) FROM zh_diversity_report WHERE status=1 and is_protection = 0 and province = '贵州' AND city = ?1 and county = ?2",nativeQuery = true)
    Integer getCountByCityAndCounty(String city, String county);

    @Query(value = "SELECT id,title,create_time,update_time FROM zh_diversity_report WHERE status=1 and is_protection = 0 and province = '贵州' AND city = ?1  ORDER BY ?#{#pageable}",
            countQuery = "SELECT count(*) FROM zh_diversity_report WHERE status=1 and is_protection = 0 and province = '贵州' AND city = ?1 ",nativeQuery = true)
    List<Object> getPageListByCity(String city, Pageable pageable);

    @Query(value = "SELECT count(id) FROM zh_diversity_report WHERE status=1 and is_protection = 0 and province = '贵州' AND city = ?1",nativeQuery = true)
    Integer getCountByCity(String city);

    @Query(value = "SELECT id,title,create_time,update_time FROM zh_diversity_report WHERE status=1 and is_protection = 1 ORDER BY ?#{#pageable}",
            countQuery = "SELECT count(*) FROM zh_diversity_report WHERE status=1 and is_protection = 1 ",nativeQuery = true)
    List<Object> getPageList(Pageable pageable);

    @Query(value = "SELECT count(id) FROM zh_diversity_report WHERE status=1 and is_protection = 1",nativeQuery = true)
    Integer getCount();

    @Query(value = "SELECT count(id) FROM zh_diversity_report WHERE status=1 AND if(?1!='',title LIKE concat('%',?1,'%'),1=1)",nativeQuery = true)
    Integer getCountByTitle(String title);

    @Query(value = "SELECT id,title,create_time,update_time FROM zh_diversity_report WHERE status=1  AND if(?1!='',title LIKE concat('%',?1,'%'),1=1) ORDER BY ?#{#pageable}",
            countQuery = "SELECT count(*) FROM zh_diversity_report WHERE status=1 AND if(?1!='',title LIKE concat('%',?1,'%'),1=1) ",nativeQuery = true)
    List<Object> getAllListByTitle(String title,Pageable pageable);


    //查询对应物种id
    @Query(value = "select u.organism_id from Taxonomy u where u.kingdom LIKE concat('%','动物','%') group by organism_id",nativeQuery = true)
    List<Long> getAnimaisByKingdom1();

    @Query(value = "select u.organism_id from Taxonomy u where u.kingdom like concat('%','植物','%')  group by organism_id",nativeQuery = true)
    List<Long> getAnimaisByKingdom2();

    @Query(value = "select u.organism_id from Taxonomy u where u.kingdom='色素界' or u.kingdom='病菌界' or u.kingdom='古菌界' or u.kingdom='真菌界' or u.kingdom='细菌界' or u.kingdom='原生动物界' group by organism_id",nativeQuery = true)
    List<Long> getAnimaisByKingdom3();

    @Query(value = "select u.name,u.latin_name from Organism u where u.endemism like concat('%','中国特有','%') and id in(?1)",nativeQuery = true)
    List<Object> getAnimaisByKingdom4(List<Long> ids );

    @Query(value = "select u.name,u.latin_name from Organism u where u.endemism like concat('%','贵州狭域分布','%') and id in(?1)",nativeQuery = true)
    List<Object> getAnimaisByKingdom5(List<Long> ids);

    @Query(value = "select u.name,u.latin_name from Organism u where u.endemism like concat('%','地方特有','%') and id in(?1)",nativeQuery = true)
    List<Object> getAnimaisByKingdom6(List<Long> ids);


    @Query(value = "select organism_id from conservation_status u where u.name like concat('%','IUCN物种红色名录','%') group by organism_id",nativeQuery = true)
    List<Long> getAnimaisByKingdom7();

    @Query(value = "select organism_id from conservation_status u where u.name like concat('%','中国生物多样性红色名录','%') group by organism_id",nativeQuery = true)
    List<Long> getAnimaisByKingdom8();

    @Query(value = "select organism_id from conservation_status u where u.name like concat('%','濒危野生动植物种国际贸易公约','%') group by organism_id",nativeQuery = true)
    List<Long> getAnimaisByKingdom9();

    @Query(value = "select organism_id from conservation_status u where u.name like concat('%','国家重点保护野生动植物','%') and u.status like concat('%','I级','%') group by organism_id",nativeQuery = true)
    List<Long> getAnimaisByKingdom10();

    @Query(value = "select organism_id from conservation_status u where u.name like concat('%','国家重点保护野生动植物','%') and u.status like concat('%','II级','%') group by organism_id",nativeQuery = true)
    List<Long> getAnimaisByKingdom11();

    //获取物种名称和拉丁文名
    @Query(value = "select name,latin_name from organism where id in (?1) and id in (?2) ",nativeQuery = true)
    List<Object> getNameAndLatinByKingdom1(List<Long> ids,List<Long> ids1);

    //获取国家重点保护野生动植物
    @Query(value = "select name,latin_name from organism where id in (?1) and id in (?2)  and id in (?3)",nativeQuery = true)
    List<Object> getNameAndLatinByKingdom2(List<Long> ids,List<Long> ids1,List<Long> ids2);

//    @Query(value = "SELECT county FROM zh_diversity_report WHERE status=1 and is_protection = 1 group by county",nativeQuery = true)
//    List<String> get();

    @Query(value = "SELECT id,title,create_time,update_time FROM zh_diversity_report WHERE status=1 and is_protection = 1 AND city = ?1 and county = ?2 ORDER BY ?#{#pageable}",
            countQuery = "SELECT count(*) FROM zh_diversity_report WHERE status=1 and is_protection = 1 AND city = ?1 and county = ?2",nativeQuery = true)
    List<Object> getPageList(String city, String county, Pageable pageable);

    @Query(value = "SELECT count(id) FROM zh_diversity_report WHERE status=1 and is_protection = 1 AND city = ?1 and county = ?2",nativeQuery = true)
    Integer getCount(String city, String county);

    @Query(value = "SELECT count(id) FROM zh_diversity_report WHERE if(?1!='',title LIKE concat('%',?1,'%'),1=1) AND if(?2 !='',author like concat('%',?2,'%'),1=1)",nativeQuery = true)
    Integer getCountByTitle1(String title,String creator);

    @Query(value = "SELECT id,title,create_time,update_time,view_count,status FROM zh_diversity_report WHERE if(?1!='',title LIKE concat('%',?1,'%'),1=1) AND if(?2 !='',author like concat('%',?2,'%'),1=1) ORDER BY ?#{#pageable}",
            countQuery = "SELECT count(*) FROM zh_diversity_report WHERE if(?1!='',title LIKE concat('%',?1,'%'),1=1) AND if(?2 !='',author like concat('%',?2,'%'),1=1) ",nativeQuery = true)
    List<Object> getAllListByTitle1(String title,String creator,Pageable pageable);

    //查询大型真菌数量
    @Query(value = "select id from organism WHERE use_values like concat('%',?1,'%') and id in (?2)",nativeQuery = true)
    List<Long> getDaXingZhenJunNumber(String name,List<Long> areaId);

    @Query(value = "SELECT count(DISTINCT(t.t_class)) as 纲,count(DISTINCT(t.t_order)) as 目, count(DISTINCT(t.family)) as 科,COUNT(DISTINCT(t.genus)) as 属,COUNT(t.species) as 种 FROM taxonomy t WHERE t.organism_id in (?1)",nativeQuery = true)
    public Object getDaXingZhenJun(List<Long> ids);

    @Query(value = "SELECT title FROM zh_diversity_report WHERE id=?1",nativeQuery = true)
    String findTitleById(Long id);
}
